<?php
class proyecto extends CI_Controller {
	var $titp='Proyectos';
	var $url ='proyecto/';

	function index(){
		redirect($this->url.'filteredgrid');
	}

	function filteredgrid(){
		$this->load->library('rapyd');

		$this->db->select(array('b.id AS idgrup','b.nombre','a.id','a.nombre AS value'));
		$this->db->from('parroquias AS a');
		$this->db->join('municipios AS b','a.id_municipios=b.id');
		$this->db->order_by('b.nombre, a.nombre');
		$query = $this->db->get();
		$opts=array();
		if ($query->num_rows() > 0){
			foreach ($query->result() as $row){
				$opts[$row->nombre][$row->id]=$row->value;
			}
		}

		$filter = new datafilter_library();
		$filter->label = $this->titp;
		$filter->db->select(array('a.*','b.nombre AS ubica','c.nombre AS eje'));
		$filter->db->from('proyectos  AS a');
		$filter->db->join('parroquias AS b','a.id_parroquia=b.id');
		$filter->db->join('zonas AS c','a.id_zonas=c.id');

		$filter->field('input','a.nombre'  ,'Nombre')
			->attributes(array('style' => 'width:170px'));

		$filter->field('input','a.numero'  ,'Número')
			->attributes(array('style' => 'width:170px'));

		$filter->field('dropdown','id_parroquia','Parroquia')
			->rule('required')
			->option('','Seleccionar')
			->options($opts)
			->attributes(array('style'=>'width: 300px'))
			->set_group('Ubicación');

		$filter->field('dropdown','id_zonas','Eje')
			->rule('required')
			->option('','Seleccionar')
			->options('SELECT id, nombre FROM zonas ORDER BY nombre')
			->attributes(array('style'=>'width: 300px'))
			->set_group('Ubicación');

		$filter->buttons('reset', 'search');
		$filter->build();

		$grid = new datagrid_library();
		$grid->label = 'Lista de proyectos registrados';
		$grid->per_page = 40;
		$grid->cid = '';
		$grid->source($filter);

		$grid->column('nombre','Nombre',true)
			->url($this->url.'dataedit/show/{id}');

		$grid->column('numero','Número',true);

		$grid->column('ubica','Parroquia',true);

		$grid->column('eje','Eje',true);

		$grid->column('monto' ,'Monto' ,true)
			->attributes(array('align'=>'right'));

		$link=anchor_popup('proyecto/formatos/{id}','Imprimir',array());
		$grid->column($link ,'Imprimir' ,true)
			->attributes(array('align'=>'right'));

		$grid->add_button(array('url'=>$this->url.'dataedit/create'));
		$action = "javascript:window.location.href='".site_url('/')."'";
		$grid->button('btn_reg', 'Regresar', $action, 'TR');
		$grid->build();

		$data['content'] = $filter->output.$grid->output;
		$data['head']    = script('jquery.js').$this->rapyd->head();
		$data['title']   = '';
		$this->load->view('view_ventanas_sola', $data);
	}

	function dataedit(){
		$this->load->library('rapyd');

		$this->db->select(array('b.id AS idgrup','b.nombre','a.id','a.nombre AS value'));
		$this->db->from('parroquias AS a');
		$this->db->join('municipios AS b','a.id_municipios=b.id');
		$this->db->order_by('b.nombre, a.nombre');
		$query = $this->db->get();
		$opts=array();
		if ($query->num_rows() > 0){
			foreach ($query->result() as $row){
				$opts[$row->nombre][$row->id]=$row->value;
			}
		}
		

		$edit = new dataedit_library();
		$edit->label = $this->titp;
		$edit->back_url = site_url($this->url.'filteredgrid');

		$edit->source('proyectos');
		$edit->pre_process(array('insert'), array($this, '_pre_insert'));

		$edit->field('input','nombre' ,'Nombre del proyecto')
			->rule('ucwords|trim|required');

		$edit->field('input','numero' ,'Número')
			->rule('ucwords|trim|required');

		$edit->field('input','memo' ,'Número de memo')
			->rule('ucwords|trim|required');

		$edit->field('date','fecha','Fecha')
			->rule('required')
			->attributes(array('style'=>'width: 130px'))
			->set_insert_value(date('Y-m-d'));

		$edit->field('dropdown','id_promotor','Promotor')
			->rule('required')
			->option('','Seleccionar')
			->options('SELECT id,nombre FROM promotores ORDER BY nombre')
			->attributes(array('style'=>'width: 300px'));

		$edit->field('dropdown','id_comite','Comité')
			->rule('required')
			->option('','Seleccionar')
			->options('SELECT id,nombre FROM comite ORDER BY nombre')
			->attributes(array('style'=>'width: 200px'));

		$edit->field('dropdown','status','Estatus')
			->options(array(
				'R'=>'Espera recurso',
				'E'=>'Ejecución',
				'C'=>'Cerrado',
				'T'=>'Terminado',
				'P'=>'Paralizado'))
			->set_insert_value('R');

		$edit->field('dropdown','sistema','Sistema')
			->options(array(
				'F'=>'Forestal',
				'A'=>'Agroforestal',
				'P'=>'Protector',
				'C'=>'Comercial'))
			->set_insert_value('F');

		$edit->field('dropdown','face','Face')
			->options(array(
				'V'=>'Vivero',
				'P'=>'Plantación',
				'M'=>'Mantenimiento'))
			->set_insert_value('V');

		$edit->field('input','meta' ,'Meta de cultivos')
			->rule('numeric');

		$edit->field('input','hectareas_recu' ,'Hectareas a recuperar')
			->rule('numeric');

		$edit->field('textarea', 'observacion','Observación' );

		$edit->field('dropdown','id_parroquia','Parroquia')
			->rule('required')
			->option('','Seleccionar')
			->options($opts)
			->attributes(array('style'=>'width: 300px'))
			->set_group('Ubicación');

		$edit->field('dropdown','id_zonas','Eje')
			->rule('required')
			->option('','Seleccionar')
			->options('SELECT id, nombre FROM zonas ORDER BY nombre')
			->attributes(array('style'=>'width: 300px'))
			->set_group('Ubicación');

		$edit->field('input','monto','Monto total')
			->rule('numeric|required')
			->set_attributes(array('size'=>'15'))
			->set_group('Datos Financieros');

		$edit->field('container','rendicion','rendicion')
			->set_group('Datos Financieros');

		$edit->field('container','desembolso','desembolso')
			->set_group('Datos Financieros');

		$edit->field('container','cultivos','cultivos');

		$edit->field('container','fotos','fotos');

		$edit->buttons('save','undo','back','modify');
		$edit->build();

        if ($this->rapyd->uri->value('show')){
			if ($this->rapyd->uri->value('show1|modify1|update1|create1|insert1|do_delete1')){
				$edit->nest('rendicion',$this->rendicion());
			}else {
				$edit->nest('rendicion',$this->rendiciones());
			}
        }

        if ($this->rapyd->uri->value('show')){
			if ($this->rapyd->uri->value('show2|modify2|update2|create2|insert2|do_delete2')){
				$edit->nest('desembolso',$this->desembolso());
			}else {
				$edit->nest('desembolso',$this->desembolsos());
			}
        }

        if ($this->rapyd->uri->value('show')){
			if ($this->rapyd->uri->value('show3|modify3|update3|create3|insert3|do_delete3')){
				$edit->nest('cultivos',$this->cultivo());
			}else {
				$edit->nest('cultivos',$this->cultivos());
			}
        }

        if ($this->rapyd->uri->value('show')){
			if ($this->rapyd->uri->value('show4|modify4|update4|create4|insert4|do_delete4')){
				$edit->nest('fotos',$this->foto());
			}else {
				$edit->nest('fotos',$this->fotos());
			}
        }

		$data['head']    = script('jquery.js').$this->rapyd->head();
		$data['title']   = '<h1>Ficha de proyectos</h1>';
		$data['content'] = $edit;
		$this->load->view('view_ventanas_sola', $data);
	}

	function fotos(){
        $grid = new datagrid_library();
        $grid->label = 'Lista de fotos';
        $grid->source('fotos');
		$grid->db->where('id_proyecto',$this->rapyd->uri->value('create|show|modify|update'));


		$url=base_url().'fotos/{nombre}';
		$onclick="javascript:window.open('${url}','foto','menubar=0,resizable=1,width=350,height=400')";
		$grid->db->orderby('fecha');
		$grid->column("<img width=200 src='${url}' onclick=\"${onclick}\" />"    , 'Foto'    ,true);
		$grid->column('fecha'      , 'Fecha'      ,true);
		$grid->column('comentario' , 'Comentario' ,true);

		$grid->column('Eliminar', '&nbsp;')
			->url($this->url.'dataedit/show/{id_proyecto}/do_delete4/{id}');
        $grid->buttons('add');
		$grid->build();

		return $grid->output;
	}

	function foto(){
		$this->load->library('rapyd');

		$edit = new dataedit_library();
		$edit->back_url = site_url($this->url.'dataedit/show/'.$this->rapyd->uri->value('show'));
		$edit->back_save = true;
		$edit->back_delete = true;
		$edit->back_cancel = true;
		$edit->back_cancel_save = true;
		$edit->label = 'Ficha para el registro de produci&oacute;n;';

		$edit->source('fotos');

		$edit->field('date','fecha','Fecha')
			->rule('required')
			->attributes(array('style'=>'width: 150px'));

		$edit->field('textarea','comentario','Comentario');

		$edit->field('upload','nombre','Foto')
			->set_upload_path('fotos')
			->set_allowed_types('jpg')
			->mode='autohide';

		$edit->field('hidden','id_proyecto','')->insert_value($this->rapyd->uri->value('show'));

		$edit->buttons('save','undo','back','modify');
		$edit->build();

		return $edit->output;
	}

	function cultivos(){
        $grid = new datagrid_library();
        $grid->label = 'Lista de cultivos';
        //$grid->source('cultivos');
        $grid->db->select(array('a.id_proyecto','a.id','a.cantidad','a.fecha','b.nombre AS especie'));
        $grid->db->from('cultivos  AS a');
        $grid->db->join('especiesp AS b','a.id_especiesp = b.id');
		$grid->db->where('a.id_proyecto',$this->rapyd->uri->value('create|show|modify|update'));

		$grid->db->orderby('fecha');
		$grid->column('especie'     , 'Especie'    ,true);
		$grid->column('fecha'       , 'Fecha'      ,true);
		$grid->column('cantidad'    , 'Cantidad'   ,true)
			->attributes(array('align'=>'right'));

		$grid->column('Modificar', '&nbsp;')
			->url($this->url.'dataedit/show/{id_proyecto}/modify3/{id}');
		$grid->column('Eliminar', '&nbsp;')
			->url($this->url.'dataedit/show/{id_proyecto}/do_delete3/{id}');
        $grid->buttons('add');
		$grid->build();

		return $grid->output;
	}

	function cultivo(){
		$this->load->library('rapyd');

		$edit = new dataedit_library();
		$edit->back_url = site_url($this->url.'dataedit/show/'.$this->rapyd->uri->value('show'));
		$edit->back_save = true;
		$edit->back_delete = true;
		$edit->back_cancel = true;
		$edit->back_cancel_save = true;
		$edit->label = 'Ficha para el registrio de produci&oacute;n;';

		$edit->source('cultivos');

		$edit->field('dropdown','id_especiesp','Especie')
			->options('SELECT id,nombre FROM especiesp ORDER BY nombre')
			->rule('required');

		$edit->field('date','fecha','Fecha')
			->rule('required')
			->attributes(array('style'=>'width: 150px'));

		$edit->field('input','cantidad','Cantidad')
			->rule('numeric|required')
			->set_attributesv(array('size'=>'15'));

		$edit->field('hidden','id_proyecto','')->insert_value($this->rapyd->uri->value('show'));

		$edit->buttons('save','undo','back','modify');
		$edit->build();

		return $edit->output;
	}

	function rendiciones(){
        $grid = new datagrid_library();
        $grid->label = 'Lista de egresos para rendición de cuenta';
        $grid->source('rendicion');

		$grid->db->where('id_proyecto',$this->rapyd->uri->value('create|show|modify|update'));
		$grid->db->orderby('fecha');
		$grid->column('tipo'        , 'Tipo'       ,true);
		$grid->column('numero'      , 'Número'     ,true);
		$grid->column('fecha'       , 'Fecha'      ,true);
		$grid->column('descripcion' , 'Descripción',true);
		$grid->column('monto'       , 'Monto',true)
			->attributes(array('align'=>'right'));
		$grid->column('Modificar', '&nbsp;')
			->url($this->url.'dataedit/show/{id_proyecto}/modify1/{id}');
		$grid->column('Eliminar', '&nbsp;')
			->url($this->url.'dataedit/show/{id_proyecto}/do_delete1/{id}');
        $grid->buttons('add');
		$grid->build();

		return $grid->output;
	}

	function rendicion(){
		$this->load->library('rapyd');

		$edit = new dataedit_library();
		$edit->back_url = site_url($this->url.'dataedit/show/'.$this->rapyd->uri->value('show'));
		$edit->back_save = true;
		$edit->back_delete = true;
		$edit->back_cancel = true;
		$edit->back_cancel_save = true;
		$edit->label = 'Ficha para la rendción de cuentas';

		$edit->source('rendicion');
		$edit->pre_process(array('insert'), array($this, 'pre_rendicion_insert'));

		$edit->field('input','numero' ,'Número')
			->rule('trim|required');

		$edit->field('dropdown','tipo','Tipo')
			->options(array(
				'R'=>'Recibo',
				'F'=>'Factura',
				'C'=>'Comision bancaria'))
			->rule('required')
			->set_insert_value('F');

		$edit->field('date','fecha','Fecha')
			->rule('required')
			->attributes(array('style'=>'width: 100px'));

		$edit->field('input','descripcion','Descripción')
			->rule('required')
			->set_attributes(array('size'=>'50'));

		$edit->field('input','monto','Monto ')
			->rule('numeric|required')
			->set_attributesv(array('size'=>'15'));

		$edit->field('hidden','id_proyecto','')->insert_value($this->rapyd->uri->value('show'));

		$edit->buttons('save','undo','back','modify');
		$edit->build();

		return $edit->output;
	}

	//Funcion para validar que la suma de las rendiciones no sea mayor al
	//monto del proyecto
	function pre_rendicion_insert($model){

		$mon= $model->get('monto');
		$id=  $model->get('id_proyecto');
		
	
		$this->db->select('SUM(a.monto) AS monto');
		$this->db->from('rendicion AS a');
		$this->db->where('a.id_proyecto',$id);
		$query= $this->db->get();
		$aux1  = $query->row();

		$this->db->select('SUM(b.monto) AS mont');
		$this->db->from('desembolso AS b');
		$this->db->where('b.id_proyecto',$id);
		$query= $this->db->get();
		$aux2  = $query->row();

		$this->db->select(array('a.*'));
		$this->db->from('proyectos AS a');
		$this->db->where('a.id',$id);
		$query= $this->db->get();
		$aux3  = $query->row();
		$comi= $aux3->id_comite;

		$saldo= $aux2->mont - $aux1->monto;

		if($aux1->monto == 0  && $aux2->mont >= $mon){

		$data = array(
               'saldo' => $saldo - $mon,
	        );
		
		$this->db->where('id', $comi);
		$this->db->update('comite', $data); 
			return true;
		}
		if($aux1->monto + $mon <= $aux2->mont) {
		$data = array(
               'saldo' => $saldo - $mon,
	        );
		
		$this->db->where('id', $comi);
		$this->db->update('comite', $data); 
			return true;
		}
		$model->error_string = '--No se puede aceptar este desembolso ya que es mayor al monto del proyecto.';
		return false;
	}


	function desembolsos(){
		$grid = new datagrid_library();
		$grid->label = 'Lista de desembolsos';
		$grid->source('desembolso');

		$grid->db->where('id_proyecto',$this->rapyd->uri->value('create|show|modify|update'));
		$grid->db->orderby('fecha');
		$grid->column('cheque' , 'Cheque'     ,true);
		$grid->column('anombre', 'A nombre de',true);
		$grid->column('nota'   , 'Nota'       ,true);
		$grid->column('fechach', 'Fecha del cheque',true);
		$grid->column('monto'  , 'Monto'      ,true)
			->attributes(array('align'=>'right'));
		$grid->column('Modificar', '&nbsp;')
			->url($this->url.'dataedit/show/{id_proyecto}/modify2/{id}');
		//$grid->column('Eliminar', '&nbsp;')
		//	->url($this->url.'dataedit/show/{id_proyecto}/do_delete2/{id}');
		$grid->buttons('add');
		$grid->build();

		return $grid->output;
	}

	function desembolso(){

		$this->load->library('rapyd');

		$this->db->select(array('c.nombre AS nom','d.nombre AS integrante','c.id'));
		$this->db->from('comite AS c');
		$this->db->join('integrantes AS d','c.id=d.id_comite');
		$this->db->order_by('c.nombre, d.nombre');
		$query = $this->db->get();
		$nuevo=array();
		if ($query->num_rows() > 0){
			foreach ($query->result() as $row){
				$nuevo[$row->nom][$row->id]=$row->integrante;
			}
		}

		$edit = new dataedit_library();
		$edit->back_url = site_url($this->url.'dataedit/show/'.$this->rapyd->uri->value('show'));
		$edit->back_save = true;
		$edit->back_delete = true;
		$edit->back_cancel = true;
		$edit->back_cancel_save = true;
		$edit->label = 'Ficha para los desembolsos';

		$edit->source('desembolso','comite');
		$edit->pre_process(array('insert'), array($this, 'pre_desembolso_insert'));

		$edit->field('input','cheque' ,'Número de cheque')
			->rule('trim|required');

		/*$edit->field('input','anombre' ,'A nombre de')
			->rule('trim|required');*/

		$edit->field('dropdown','anombre','A nombre de')
			->rule('required')
			->option('','Seleccionar')
			//->options('SELECT id, nombre FROM integrantes ORDER BY nombre')
			->options($nuevo)
			->attributes(array('style'=>'width: 300px'));

		$edit->field('date','fecha','Fecha de entrega')
			->rule('required')
			->attributes(array('style'=>'width: 100px'));

		$edit->field('date','fechach','Fecha del cheque')
			->rule('required')
			->attributes(array('style'=>'width: 100px'));

		$edit->field('input','nota','Nota')
			->set_attributes(array('size'=>'50'));

		$edit->field('dropdown','id_tban','Banco')
			->rule('required')
			->option('','Seleccionar')
			->options('SELECT id, nombre FROM tban ORDER BY nombre')
			->attributes(array('style'=>'width: 300px'));

		$edit->field('input','monto','Monto ')
			->rule('numeric|required')
			->set_attributes(array('size'=>'15'));

		$edit->field('hidden','id_proyecto','')->insert_value($this->rapyd->uri->value('show'));

		$edit->buttons('save','undo','back','modify');
		$edit->build();

		return $edit->output;
	}

	//Valida que la suma de desembolsos no sea mayor
	//al monto de proyecto
	function pre_desembolso_insert($model){

		$mon= $model->get('monto');
		$id=  $model->get('id_proyecto');

		$this->db->select('SUM(a.monto) AS monto');
		$this->db->from('desembolso AS a');
		$this->db->where('a.id_proyecto',$id);
		$query= $this->db->get();
		$aux1  = $query->row();

		$this->db->select(array('b.monto AS mont','b.id_comite AS idc'));
		$this->db->from('proyectos AS b');
		$this->db->where('b.id',$id);
		$query= $this->db->get();
		$aux2  = $query->row();
		$comi= $aux2->idc;
		
		$this->db->select('SUM(c.monto) AS ren');
		$this->db->from('rendicion AS c');
		$this->db->where('c.id_proyecto',$id);
		$query= $this->db->get();
		$aux3  = $query->row();
		$saldo= $aux1->monto - $aux3->ren;

		if($aux1->monto == 0  && $aux2->mont >= $mon){

		$data = array(
               'saldo' => $saldo + $mon,
	        );
		
		$this->db->where('id', $comi);
		$this->db->update('comite', $data); 
			return true;
		}
		if($aux1->monto + $mon <= $aux2->mont) {
		$data = array(
               'saldo' => $saldo + $mon,
	        );
		
		$this->db->where('id', $comi);
		$this->db->update('comite', $data); 
			return true;
		}
		$model->error_string = '--No se puede aceptar este desembolso ya que es mayor al monto del proyecto.';
		return false;

	}

	function _pre_insert($model){
		//$model->set('desem1',0);
		//$model->set('desem2',0);
		return true;
	}

	function get_municipios(){
		$estado=$this->input->post('env_estado');
		$rt='<option value"">Seleccionar</option>';
		if($estado!==false){
			$sel=array('a.id','a.nombre');
			$this->db->select($sel);
			$this->db->from('municipios AS a');
			$this->db->order_by('a.nombre');
			$this->db->where('a.id_estado' ,$estado);
			$query = $this->db->get();
			if ($query->num_rows() > 0){
				foreach ($query->result() as $row){
					$rt.= '<option value="'.$row->id.'">'.$row->nombre.'</option>';
				}
			}
		}
		echo $rt;
	}

	function get_parroquias(){
		$municipios=$this->input->post('env_municipios');
		$rt='<option value"">Seleccionar</option>';
		if($municipios!==false){
			$sel=array('a.id','a.nombre');
			$this->db->select($sel);
			$this->db->from('parroquias AS a');
			$this->db->order_by('a.nombre');
			$this->db->where('a.id_municipios' ,$municipios);
			$query = $this->db->get();
			if ($query->num_rows() > 0){
				foreach ($query->result() as $row){
					$rt.= '<option value="'.$row->id.'">'.$row->nombre.'</option>';
				}
			}
		}
		echo $rt;
	}

	function formatos($id){
		$this->load->helper('date');
		$this->db->select(array('a.*','d.nombre AS comite','d.razon AS razon','d.cuenta AS cuenta','e.nombre AS bnombre'));
		$this->db->from('proyectos AS a');
		$this->db->join('parroquias AS b','a.id_parroquia=b.id');
		$this->db->join('zonas AS c'     ,'a.id_zonas=c.id');
		$this->db->join('comite AS d'    ,'a.id_comite=d.id');
		$this->db->join('tban AS e','d.id_tban','left(.)','=e.id');
		
		$this->db->where('a.id',$id);
		$query = $this->db->get();

		$data = array();
		if ($query->num_rows() > 0){
			$row  = $query->row();
			$data['nombre'] = $row->nombre;
			$data['comite'] = $row->comite;
			$data['numero'] = $row->numero;
			$data['estatus']= $row->status;
			$data['razon']  = $row->razon;
			$data['monto']  = $row->monto;
			$data['cuenta'] = $row->cuenta;
			$data['bcomite']  = $row->bnombre; 
		}

		$this->db->select(array('a.*','b.nombre AS banco','a.monto AS montode','c.nombre'));
                $this->db->from('desembolso AS a');
		$this->db->join('integrantes AS c','a.anombre=c.id');
                $this->db->join('tban AS b','a.id_tban=b.id');
		
		$this->db->where('a.id_proyecto',$id);
                $query = $this->db->get();
                $data['desem'] = $query->result();

		$this->db->select('SUM(monto) AS totdes');
		$this->db->from('desembolso');
		$this->db->where('id_proyecto',$id);
		$query = $this->db->get();
		$row2  = $query->row();
		$data['totdes']= $row2->totdes;
		
		$this->db->select('SUM(c.monto) AS montoren');
		$this->db->from('rendicion AS c');
		$this->db->where('c.id_proyecto',$id);
		$query= $this->db->get();
		$aux1  = $query->row();
		$data['renmon'] = $aux1->montoren;

		$this->load->view('formatos/admin_anual_por_proy.php', $data);
	}
}
